package net.skyedu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.skyedu.bean.AppealObject;
import net.skyedu.bean.Shensu;
import net.skyedu.bean.Uinfo;

public class AppDao extends DAO{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	/**
	 * add Appeal
	 */
//	public int addAppeal(Appeal app){
//		StringBuffer sql=new StringBuffer("insert into appeal(app_att_id,app_sendname,app_recipientid,app_content,app_createtime) values(?,?,?,?,getdate())");
//		String [] params=new String[] {app.getAppattid()+"",app.getSendname(),app.getRecipientid()+"",app.getContent()};
//		return this.executeSQL(sql.toString(), params);
//	}
	
	public Uinfo findUName(int userId){
		Uinfo obj=null;
		try{
			StringBuffer sql=new StringBuffer("select u.Name,u.tea_id,d.att_type " +
					"from USERINFO u ,DEPARTMENTS d " +
					"where u.DEFAULTDEPTID=d.DEPTID " +
					"and u.USERID="+userId);
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				obj=new Uinfo();
				obj.setUserName(rs.getString("Name"));
				obj.setSchType(rs.getInt("att_type"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return obj;
	}
	
	//查询是否为主管什么的
//	select COUNT(per_id) from att_persons where per_user_id=1741;
//	public boolean findUN(int userId){
//		boolean flag = false;
//		int num = 0;
//		try{
//			StringBuffer sql=new StringBuffer("select COUNT(per_id) from att_persons where per_user_id="+userId);
//			conn=this.getConn();
//			pstmt=conn.prepareStatement(sql.toString());
//			rs=pstmt.executeQuery();
//			while(rs.next()){
//				num = rs.getInt(1);
//			}
//		}catch(Exception e){
//			e.printStackTrace();
//		}finally{
//			this.closeAll(conn, pstmt, rs);
//		}
//		if(num!=0){
//			flag = true;
//		}
//		return flag;
//	}
	
//	select per_status from att_persons where per_user_id=1741;
	public int findUN(int userId,String str){
		int num = 0;
		try{
			StringBuffer sql=new StringBuffer("select "+str+" from att_persons where per_user_id="+userId);
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				num = rs.getInt(1);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		
		return num;
	}
//	select u.tea_id,p.per_title,p.per_user_name 
//	from USERINFO u,att_persons p 
//	where u.USERID=p.per_user_id 
//	and p.per_status = 3
//	and p.per_sch_id in (8,10)
//	order by p.per_user_name 
	
	/**查询申诉对象*/
	public List<Shensu> selDx(int schType,int perState){
		List<Shensu> list = new ArrayList<Shensu>();
		try{
			StringBuffer sql=new StringBuffer("	select u.tea_id,p.per_title,p.per_user_name " +
					"from USERINFO u,att_persons p " +
					"where u.USERID=p.per_user_id " +
					"and p.per_status = "+perState+" " +
					"and p.per_sch_id in ("+schType+",10) " +
					"order by p.per_id");
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				Shensu obj = new Shensu();
				obj.setOaId(rs.getInt("tea_id"));
				obj.setSubDept(rs.getString("per_title"));
				obj.setuName(rs.getString("per_user_name"));
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public List<Shensu> selDx(){
		List<Shensu> list = new ArrayList<Shensu>();
		try{
//			StringBuffer sql=new StringBuffer("	select u.tea_id,p.per_title,p.per_user_name " +
//					"from USERINFO u,att_persons p " +
//					"where u.USERID=p.per_user_id " +
//					"and p.per_status = "+perState+" " +
//					"and p.per_sch_id in ("+schType+",10) " +
//					"order by p.per_id");
		
		//StringBuffer sql=new StringBuffer("select u.tea_id,a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id order by u.USERID desc");
			
			StringBuffer sql=new StringBuffer("select 0 tea_id ,'项目管理中心' per_user_name "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where  u.USERID=a.per_user_id and per_depart_name='项目管理中心' "+  
						"union all "+
						"select 0,'教务中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id and per_depart_name='教务中心' "+ 
						"union all "+
						"select 0,'教学中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id and per_depart_name='教学中心' "+ 
						"union all "+
						"select 0,'人力资源中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id and per_depart_name='人力资源中心' "+ 
						"union all "+
						"select 0,'财务中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id and per_depart_name='财务中心' "+ 
						"union all "+
						"select 0,'信息技术中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u where u.USERID=a.per_user_id and per_depart_name='信息技术中心' "+ 
						"union all "+
						"select 0,'课程中心' "+
						"union all "+
						"select u.tea_id,'         '+a.per_user_name from att_personnew a,userinfo u  where u.USERID=a.per_user_id and per_depart_name='课程中心' ");
			System.out.println(sql);
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			Shensu obj1 = new Shensu();
			obj1.setOaId(0);
			obj1.setuName("请选择");
			list.add(obj1);
			while(rs.next()){
				Shensu obj = new Shensu();
				obj.setOaId(rs.getInt("tea_id"));
//				obj.setSubDept(rs.getString("per_title"));
				if(obj.getOaId()==0)
				{
					obj.setuName("------"+rs.getString("per_user_name")+"--------");
				}
				else
				{
					obj.setuName(rs.getString("per_user_name"));
				}
				System.out.println(obj.getuName());
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
	//	修改
	public List<AppealObject> showAppeal(){
		List<AppealObject> list = new ArrayList<AppealObject>();
		try{
			StringBuffer sql=new StringBuffer("select * from att_persons");
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				AppealObject obj = new AppealObject();
				obj.setPerId(rs.getInt(1));
				obj.setSchType(rs.getInt(2));
				obj.setUserId(rs.getInt(3));
				obj.setPerTitle(rs.getString(4));
				obj.setPerName(rs.getString(5));
				obj.setPerStatus(rs.getInt(6));
				list.add(obj);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	
}
